from django.core.paginator import Paginator
from django.db.models import Q
from django.http import HttpResponse
from django.views.decorators.http import require_POST, require_GET

from utils.customer_util import CustomerUtil
from utils.response_code import ResponseCode

from django.db import transaction

from course.models import Course
from clazz.models import Clazz
from .models import Student
import pandas as pd

from django.db.utils import IntegrityError

from openpyxl import Workbook


# Create your views here.
@require_POST
def student_page(request):
    params = CustomerUtil.get_request_params(request)
    page = params.get('page')
    size = params.get('size')
    student_name = params.get('student_name')
    clazz = params.get('clazz')
    course = params.get('course')
    # 构建动态sql
    condition = Q()
    if student_name.strip():
        condition &= Q(student_name__contains=student_name)
    # 根据班级名称查询 班级id
    if clazz.strip():
        clzz = Clazz.objects.filter(name__contains=clazz).first()
        if clzz:
            condition &= Q(clazz_id=clzz.id)
    # 根据课程名字查询 课程id
    if course.strip():
        curse = Course.objects.filter(course_name__contains=course).first()
        if curse:
            condition &= Q(course_id=curse.id)
    students = Student.objects.filter(condition).order_by('-id')
    paginator = Paginator(students, size)
    total = paginator.count
    current_page = paginator.page(page)
    students = list(current_page.object_list)
    print(students)
    student_list = []
    for student in students:
        item = {}
        item['id'] = student.id
        item['student_name'] = student.student_name
        item['student_no'] = student.student_no
        item["student_age"] = student.student_age
        item["student_gender"] = student.get_student_gender_display()
        item["school_time"] = student.school_time.strftime("%Y-%m-%d")
        item['course'] = student.course.course_name
        item['clazz'] = student.clazz.name
        student_list.append(item)

    return CustomerUtil.response_json({
        'code': ResponseCode.BUSINESS_SUCCESS,
        "table_data": student_list,
        "total": total
    })


@require_GET
def student_one(request, id):
    student = Student.objects.filter(id=id).values().first()
    print(student)
    return CustomerUtil.response_json({
        'code': ResponseCode.BUSINESS_SUCCESS,
        'student': student
    })


@require_POST
def student_insert_edit(request):
    form_data = CustomerUtil.get_request_params(request)
    try:
        if form_data.get('id'):
            Student.objects.filter(id=form_data.get('id')).update(**form_data)
        else:
            Student(**form_data).save()
        response = {
            'code': ResponseCode.BUSINESS_SUCCESS,
            'message': "操作成功"
        }
    except Exception as e:
        response = {
            'code': ResponseCode.BUSINESS_ERROR,
            'message': str(e)
        }

    return CustomerUtil.response_json(response)


@require_GET
def student_delete(request, id):
    Student.objects.filter(id=id).delete()
    return CustomerUtil.response_json({
        'code': ResponseCode.BUSINESS_SUCCESS,
        'message': '删除成功'
    })


"""
批量导入   所有的数据的添加 需要多次执行sql语句  那么多条sql语句就构成了一个事务
commit    提交
rollback  回滚

Django 对事务操作有支持  需要控制事务的函数上添加装饰器即可 

mysql  5.5  支持事务操作    事务隔离级别   可重复读 
事务的隔离级别    student     3
1. 读未提交
     A     insert   student   commit  rollback   4   3
     B     select   student    4    3  脏读    不可重复度
2. 读已提交
      A     insert   student   commit  rollback   4   3
      B     select   student    3    4    不可重复度   在一次事务中两次相同的查询结果不一致   
3. 可重复度   mysql默认
      A     insert   student   commit  rollback   4   3
      B     select   student    3   3  可重复度   在一次事务中两次相同的查询结果一致   
5. 串行化   数据库在同一个时间只允许一个事务操作  安全性最高   效率最低 
     A     insert   student   commit  rollback   4   3
     B     select   student   等待
     
     
事务特点：
   1.原子性   
   2.一致性
       1000   900
       1000   1100
   3.隔离性
   4.持久性 
"""


@require_POST
#  Django 提供的事务控制
@transaction.atomic
def student_import(request):
    """数据的批量导入"""
    # 上传文件
    file = request.FILES['file']
    # 如何解析文件中的数据  pandas
    df = pd.read_excel(file)
    for index, row in df.iterrows():
        item = {}
        item['student_no'] = row['学号']
        item['student_name'] = row['姓名']
        item['student_age'] = row['年龄']
        item['student_gender'] = 1 if row['性别'] == '男' else 2
        item['school_time'] = row['入学时间']

        course_name = row['课程']
        # 根据课程名 查询课程信息
        course = Course.objects.filter(course_name=course_name).first()
        if course:
            item['course_id'] = course.id
        else:
            return CustomerUtil.response_json({
                'code': ResponseCode.BUSINESS_ERROR,
                'message': '添加的课程不存在'
            })
        # 根据班级名字 查询班级详情
        clazz_name = row['班级']
        clazz = Clazz.objects.filter(name=clazz_name).first()
        if clazz:
            item['clazz_id'] = clazz.id
        else:
            return CustomerUtil.response_json({
                'code': ResponseCode.BUSINESS_ERROR,
                'message': '添加的班级不存在'
            })

        try:
            Student(**item).save()
        except IntegrityError as e:
            return CustomerUtil.response_json({
                'code': ResponseCode.BUSINESS_ERROR,
                'message': str(e)
            })
    return CustomerUtil.response_json({
        'code': ResponseCode.BUSINESS_SUCCESS,
        'message': '数据导入成功'
    })


"""
设置响应类型为表格
 application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
"""


@require_GET
def student_export(request):
    """
    :param request:   student_name   clazz  course
    :return:   excel文件
    """
    student_name = request.GET.get('student_name')
    course = request.GET.get('course')
    clazz = request.GET.get('clazz')
    # 构建动态sql
    condition = Q()
    if student_name.strip():
        condition &= Q(student_name__contains=student_name)
    # 根据班级名称查询 班级id
    if clazz.strip():
        clzz = Clazz.objects.filter(name__contains=clazz).first()
        if clzz:
            condition &= Q(clazz_id=clzz.id)
    # 根据课程名字查询 课程id
    if course.strip():
        curse = Course.objects.filter(course_name__contains=course).first()
        if curse:
            condition &= Q(course_id=curse.id)
    #  条件查询
    students = Student.objects.filter(condition).order_by('-id')
    #  将students数据以excel格式响应到前端   pip install openpyxl
    # 创建工作簿
    workbook = Workbook()
    # 获取工作表
    worksheet = workbook.active
    # 设置工作表名
    worksheet.title = "学生信息表"
    # 表头
    header = ['学号', '姓名', '年龄', '性别', '课程', '班级', '入学时间']
    worksheet.append(header)

    # 遍历students
    for student in students:
        row = [
            student.student_no,
            student.student_name,
            student.student_age,
            student.get_student_gender_display(),
            student.course.course_name,
            student.clazz.name,
            student.school_time.strftime('%Y-%m-%d')
        ]
        worksheet.append(row)

    # 创建HttpResponse对象 并声明响应的格式为excel文件
    response = HttpResponse(workbook, content_type='application/vnd.ms-excel')
    # 响应对象
    workbook.save(response)
    return response
